﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Globalization;

namespace UtilityLibrary.Ormapping
{
    /// <summary>
    /// 数据访问接口
    /// </summary>
    public sealed class SQLiteDataBase : IDisposable, IDataBase
    {
        #region --域成员--

        private int _executeRowCount;           //执行sql语句后受影响的行数
        private SQLiteCommand _cm;              //Command对象
        private SQLiteTransaction _sqlTrans;       //事务对象
        private static bool _isUseTrans;        //是否使用事务

        #endregion

        #region  --公共属性--

        /// <summary>
        /// 连接字符串
        /// </summary>
        public String ConnectionString
        {
            get { return _cm.Connection.ConnectionString; }
            set { _cm.Connection.ConnectionString = value; }
        }

        /// <summary>
        /// 受影响行数
        /// </summary>
        public int ExecuteRowCount
        {
            get { return _executeRowCount; }
        }

        #endregion

        #region --构造函数 释放资源--

        /// <summary>
        /// 构造函数
        /// </summary>
        public SQLiteDataBase()
        {
            //初始化连接
            _cm = new SQLiteCommand();
            _cm.Connection = new SQLiteConnection();
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="cnstr">连接字符串</param>
        public SQLiteDataBase(String cnstr)
        {
            //初始化连接
            _cm = new SQLiteCommand();
            _cm.Connection = new SQLiteConnection(cnstr);
        }

        /// <summary>
        /// 释放资源,实现IDisposable 成员
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        private void Dispose(Boolean disposing)
        {
            lock (this)
            {
                //清空command对象的参数
                _cm.Parameters.Clear();
                //关闭连接
                _cm.Connection.Close();
                if (!disposing) return;

                //销毁事务对象
                if (_isUseTrans) _sqlTrans.Dispose();
                //释放command对象
                _cm.Dispose();
            }
        }
        ~SQLiteDataBase()
        {
            Dispose(false);
        }

        /// <summary>
        /// 关闭与数据库的连接
        /// </summary>
        public void CloseConnection()
        {
            if (_cm.Connection.State != ConnectionState.Closed && _cm.Connection.State != ConnectionState.Broken) _cm.Connection.Close();
        }

        /// <summary>
        /// 打开与数据库的连接
        /// </summary>
        public void OpenConnection()
        {
            if (_cm.Connection.State == ConnectionState.Closed || _cm.Connection.State == ConnectionState.Broken) _cm.Connection.Open();
        }

        #endregion

        #region  --私有函数--

        /// <summary>
        /// 设置查询语句
        /// </summary>
        /// <param name="CommandText">查询语句</param>
        /// <param name="SqlType">查询类型</param>
        private void SetCommandText(String CommandText, CommandType SqlType)
        {
            //设置受影响行数为0                             
            _executeRowCount = 0;
            //设置查询语句                        
            _cm.CommandText = CommandText;
            //设置查询类型               
            _cm.CommandType = SqlType;
        }

        #endregion

        #region  --事务处理--

        /// <summary>
        /// 打开连接,开始使用事务
        /// </summary>
        public void TranBegin()
        {
            _cm.Connection.Open();
            _sqlTrans = _cm.Connection.BeginTransaction();
            _cm.Transaction = _sqlTrans;
            _isUseTrans = true;
        }

        /// <summary>
        /// 提交事务,关闭连接
        /// </summary>
        public void TranCommit()
        {
            _sqlTrans.Commit();
            _cm.Connection.Close();
        }

        /// <summary>
        /// 回滚事务,关闭连接
        /// </summary>
        public void TranRollBack()
        {
            _sqlTrans.Rollback();
            _cm.Connection.Close();
        }

        #endregion

        #region --运行查询语句返回记录（DataSet、DataTable、DataRow、Object）--

        /// <summary>
        /// 执行查询语句返回DataSet
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataSet数据集</returns>
        public DataSet RunSqlDataSet(String sql)
        {
            SetCommandText(sql, CommandType.Text);
            SQLiteDataAdapter da = new SQLiteDataAdapter(_cm);
            try
            {
                DataSet ds = new DataSet();
                ds.Locale = CultureInfo.CurrentCulture;
                da.Fill(ds);
                return ds;
            }
            finally
            {
                //释放资源
                da.Dispose();
            }
        }

        /// <summary>
        /// 执行查询语句返回DataTable
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataTable数据集</returns>
        public DataTable RunSqlDataTable(String sql)
        {
            DataSet ds = RunSqlDataSet(sql);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];

            return null;
        }

        /// <summary>
        /// 执行查询语句返回第一行DataRow
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataRow数据集</returns>
        public DataRow RunSqlDataRow(String sql)
        {
            DataTable dt = RunSqlDataTable(sql);
            if (dt != null && dt.Rows.Count > 0) return dt.Rows[0];

            return null;
        }

        /// <summary>
        /// 执行查询语句返回第一行第一列值
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>值</returns>
        public Object RunSqlObject(String sql)
        {
            SetCommandText(sql, CommandType.Text);
            Object returnValue = null;
            try
            {
                if (_cm.Connection.State == ConnectionState.Broken || _cm.Connection.State == ConnectionState.Closed) _cm.Connection.Open();
                returnValue = _cm.ExecuteScalar();
            }
            finally
            {
                _cm.Connection.Close();
            }
            return returnValue;
        }

        #endregion

        #region --执行查询语句使用out IDataReader得到数据集--

        /// <summary>
        /// 使用DataReader记录查询
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sqlDataReader">out IDataReader参数</param>
        public IDataReader RunSqlDataReader(String sql)
        {
            IDataReader dataReader = null;
            SetCommandText(sql, CommandType.Text);
            if (_cm.Connection.State == ConnectionState.Broken || _cm.Connection.State == ConnectionState.Closed) _cm.Connection.Open();
            if (_isUseTrans)
            {
                dataReader = _cm.ExecuteReader();
            }
            else
            {
                dataReader = _cm.ExecuteReader(CommandBehavior.CloseConnection);
            }

            return dataReader;
        }

        #endregion

        #region  --执行查询语句不返回记录--

        /// <summary>
        /// 执行查询语句不返回记录
        /// </summary>
        /// <param name="sql">查询语句</param>
        public void RunSql(String sql)
        {
            SetCommandText(sql, CommandType.Text);
            try
            {
                if (_cm.Connection.State == ConnectionState.Broken || _cm.Connection.State == ConnectionState.Closed) _cm.Connection.Open();
                _executeRowCount = _cm.ExecuteNonQuery();
            }
            finally
            {
                //关闭连接
                if (!_isUseTrans) _cm.Connection.Close();
            }
        }

        #endregion

        #region  --设置获取Command参数--

        /// <summary>
        /// 设置Command参数
        /// </summary>
        /// <param name="parameter">参数</param>
        public void MakeParam(IDataParameter parameter)
        {
            SQLiteParameter param = parameter as SQLiteParameter;
            if (param != null) _cm.Parameters.Add(param);
        }

        public void MakeParam(String pName, Object value)
        {
            try
            {
                _cm.Parameters[pName].Value = value;
            }
            catch
            {
                _cm.Parameters.AddWithValue(pName, value);
            }
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="Index">下标</param>
        /// <returns>对应参数</returns>
        public IDataParameter GetParam(int index)
        {
            return _cm.Parameters[index];
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="Name">参数名称</param>
        /// <returns>对应参数</returns>
        public IDataParameter GetParam(String name)
        {
            return _cm.Parameters[name];
        }

        /// <summary>
        /// 清除Command对象参数
        /// </summary>
        public void ClearParam()
        {
            this._cm.Parameters.Clear();
        }

        #endregion
    }
}
